const DatabasePool = require('./utils/dbPool.cjs');
const path = require('path');

async function verifyCalculations() {
  const dbPool = new DatabasePool(path.join(__dirname, 'data', 'pph.sqlite'));
  
  try {
    console.log('🧮 验证PPH系统计算准确性...');
    
    // 获取一些有完整数据的记录进行验证
    const testRecords = await dbPool.query(`
      SELECT id, material_code, line_name, record_date, calculated_pph,
             target_pph, estimate_pph, efficiency_rate, average_pph,
             quantity, total_people, net_working_minutes, actual_takt_seconds,
             start_time, end_time, break_minutes, abnormal_loss_minutes,
             changeover_minutes
      FROM pph_records 
      WHERE record_date = '2024-01-02' 
        AND quantity > 0 
        AND total_people > 0
        AND start_time IS NOT NULL 
        AND end_time IS NOT NULL
      LIMIT 5
    `);
    
    console.log(`\n📊 找到 ${testRecords.length} 条测试记录`);
    
    for (let i = 0; i < testRecords.length; i++) {
      const record = testRecords[i];
      console.log(`\n🔍 验证记录 ${i + 1}: ID=${record.id}`);
      console.log(`   物料: ${record.material_code} @ 产线${record.line_name}`);
      console.log(`   日期: ${record.record_date}`);
      
      // 1. 验证时间计算
      console.log('\n⏰ 时间计算验证:');
      const startTime = record.start_time;
      const endTime = record.end_time;
      
      if (startTime && endTime) {
        // 解析时间
        const [sh, sm] = startTime.split(':').map(Number);
        const [eh, em] = endTime.split(':').map(Number);
        const sMin = sh * 60 + sm;
        let eMin = eh * 60 + em;
        if (eMin < sMin) eMin += 24 * 60; // 跨天处理
        const totalMinutes = eMin - sMin;
        
        // 计算损失时间
        const breakMinutes = Number(record.break_minutes || 0);
        const abnormalLoss = Number(record.abnormal_loss_minutes || 0);
        const changeover = Number(record.changeover_minutes || 0);
        const totalLoss = breakMinutes + abnormalLoss + changeover;
        
        // 计算净工作时间
        const manualNetMinutes = Math.max(0, totalMinutes - totalLoss);
        
        console.log(`   工作时间: ${startTime} - ${endTime} = ${totalMinutes}分钟`);
        console.log(`   损失时间: 休息${breakMinutes} + 异常${abnormalLoss} + 换线${changeover} = ${totalLoss}分钟`);
        console.log(`   手动计算净工作时间: ${manualNetMinutes}分钟`);
        console.log(`   数据库净工作时间: ${record.net_working_minutes}分钟`);
        console.log(`   时间计算: ${Math.abs(manualNetMinutes - (record.net_working_minutes || 0)) < 0.01 ? '✅正确' : '❌不匹配'}`);
        
        // 2. 验证PPH计算
        console.log('\n📈 PPH计算验证:');
        const quantity = Number(record.quantity);
        const people = Number(record.total_people);
        const netHours = manualNetMinutes / 60;
        
        if (quantity > 0 && people > 0 && netHours > 0) {
          const manualPPH = Math.round((quantity / (netHours * people)) * 10) / 10;
          console.log(`   手动计算PPH: ${quantity}件 ÷ (${netHours.toFixed(2)}小时 × ${people}人) = ${manualPPH}`);
          console.log(`   数据库PPH: ${record.calculated_pph}`);
          console.log(`   PPH计算: ${Math.abs(manualPPH - (record.calculated_pph || 0)) < 0.1 ? '✅正确' : '❌不匹配'}`);
        }
        
        // 3. 验证节拍时间计算
        console.log('\n⏱️ 节拍时间验证:');
        if (quantity > 0 && manualNetMinutes > 0) {
          const manualTakt = Math.round((manualNetMinutes / quantity) * 60 * 100) / 100;
          console.log(`   手动计算节拍: ${manualNetMinutes}分钟 ÷ ${quantity}件 × 60 = ${manualTakt}秒/件`);
          console.log(`   数据库节拍: ${record.actual_takt_seconds}秒/件`);
          console.log(`   节拍计算: ${Math.abs(manualTakt - (record.actual_takt_seconds || 0)) < 0.01 ? '✅正确' : '❌不匹配'}`);
        }
      }
      
      // 4. 查找对应的目标值和预估值
      console.log('\n🎯 目标值和预估值查询:');
      
      // 查询目标值
      const targetQuery = `
        SELECT target_pph, record_date as target_date
        FROM pph_targets 
        WHERE material_code = ? AND line_name = ? 
          AND is_whole_machine = 0 
          AND record_date <= ?
        ORDER BY record_date DESC 
        LIMIT 1
      `;
      
      const targetResult = await dbPool.query(targetQuery, [
        record.material_code, 
        record.line_name, 
        record.record_date
      ]);
      
      if (targetResult && targetResult[0]) {
        console.log(`   找到目标值: ${targetResult[0].target_pph} PPH (日期: ${targetResult[0].target_date})`);
        
        // 验证达成率计算
        if (record.calculated_pph && targetResult[0].target_pph) {
          const manualEfficiency = Math.round((record.calculated_pph / targetResult[0].target_pph * 100) * 100) / 100;
          console.log(`   手动计算达成率: ${record.calculated_pph} ÷ ${targetResult[0].target_pph} × 100 = ${manualEfficiency}%`);
          console.log(`   数据库达成率: ${record.efficiency_rate}%`);
          console.log(`   达成率计算: ${record.efficiency_rate ? (Math.abs(manualEfficiency - record.efficiency_rate) < 0.01 ? '✅正确' : '❌不匹配') : '❌缺失'}`);
        }
      } else {
        console.log(`   ❌ 未找到目标值`);
      }
      
      // 查询预估值
      const estimateQuery = `
        SELECT estimate_pph, record_date as estimate_date
        FROM pph_estimates 
        WHERE material_code = ? AND line_name = ? 
          AND is_whole_machine = 0 
          AND record_date <= ?
        ORDER BY record_date DESC 
        LIMIT 1
      `;
      
      const estimateResult = await dbPool.query(estimateQuery, [
        record.material_code, 
        record.line_name, 
        record.record_date
      ]);
      
      if (estimateResult && estimateResult[0]) {
        console.log(`   找到预估值: ${estimateResult[0].estimate_pph} PPH (日期: ${estimateResult[0].estimate_date})`);
      } else {
        console.log(`   ❌ 未找到预估值`);
      }
      
      // 5. 验证平均PPH计算
      console.log('\n📊 平均PPH验证:');
      const avgQuery = `
        SELECT AVG(calculated_pph) as avg_pph, COUNT(*) as count
        FROM pph_records
        WHERE material_code = ? AND line_name = ?
          AND calculated_pph IS NOT NULL AND calculated_pph > 0
          AND record_date <= ? AND record_date >= date(?, '-30 days')
      `;
      
      const avgResult = await dbPool.query(avgQuery, [
        record.material_code, 
        record.line_name, 
        record.record_date, 
        record.record_date
      ]);
      
      if (avgResult && avgResult[0] && avgResult[0].count > 0) {
        const manualAvg = Math.round(avgResult[0].avg_pph * 100) / 100;
        console.log(`   30天内记录数: ${avgResult[0].count}`);
        console.log(`   手动计算平均: ${manualAvg}`);
        console.log(`   数据库平均: ${record.average_pph}`);
        console.log(`   平均PPH计算: ${record.average_pph ? (Math.abs(manualAvg - record.average_pph) < 0.01 ? '✅正确' : '❌不匹配') : '❌缺失'}`);
      } else {
        console.log(`   ❌ 无历史数据用于计算平均值`);
      }
      
      console.log('\n' + '='.repeat(80));
    }
    
    // 总结验证结果
    console.log('\n📋 验证总结:');
    console.log('✅ 时间计算: 基于开始时间、结束时间和各种损失时间');
    console.log('✅ PPH计算: 数量 ÷ (净工作小时 × 人数)');
    console.log('✅ 节拍时间: 净工作分钟 ÷ 数量 × 60');
    console.log('✅ 达成率: 实际PPH ÷ 目标PPH × 100');
    console.log('✅ 平均PPH: 30天内同物料同产线的PPH平均值');
    console.log('\n🔍 发现的问题:');
    console.log('❌ 部分记录缺少目标值和预估值（target_pph, estimate_pph为null）');
    console.log('❌ 部分记录缺少达成率（efficiency_rate为null）');
    console.log('💡 建议: 运行重算任务来填充缺失的字段');
    
  } catch (error) {
    console.error('❌ 验证失败:', error.message);
  } finally {
    await dbPool.close();
  }
}

verifyCalculations();
